//201902104003邵潇漩
package bysj.dao;
import util.Condition;
import util.Pagination;
import bysj.domain.School;
import util.JdbcHelper;

import java.sql.*;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public final class SchoolDao {
    private static SchoolDao schoolDao = null;
    private SchoolDao() {
    }

    public static SchoolDao getInstance() {
        if (schoolDao == null){
            schoolDao = new SchoolDao();
        }
        return schoolDao;
    }

    public static Collection<School> findAll(Connection connection , List<Condition> conditionList, Pagination pagination) throws SQLException {
        Collection<School> schools = new TreeSet<School>();
        int totalNum = CommonDao.getInstance().count(connection,"school");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM school");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }
        //获得查询的预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        //执行sql查询语句并获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        //遍历结果集，根据元组创建对象，并添加到集合中
        while(resultSet.next()){
            schools.add(new School(resultSet.getInt("id"),
                    resultSet.getString("description"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks")
            ));
        }
        JdbcHelper.close(resultSet,pstmt);
        return schools;
    }

    public School find(Connection conn,Integer id) throws SQLException {
        School desiredSchool = null;
        String sql_select_id="SELECT*FROM School WHERE id=?";
        PreparedStatement pstmt = conn.prepareStatement(sql_select_id);
        pstmt.setInt(1,id);
        ResultSet resultSet=pstmt.executeQuery();
        if(resultSet.next()){
            String description=resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            desiredSchool=new School(id,description,no,remarks);
        }
        JdbcHelper.close(resultSet,pstmt);
        return desiredSchool;
    }

    public boolean update(Connection conn,School school) throws SQLException {
        //创建sql语句
        String updateSchool_sql = "UPDATE school SET description=?, no=?, remarks=? WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(updateSchool_sql);
        //为预编译参数赋值
        pstmt.setString(1,school.getDescription());
        pstmt.setString(2,school.getNo());
        pstmt.setString(3,school.getRemarks());
        pstmt.setInt(4,school.getId());
        //执行预编译语句的executeUpdate方法,获得修改的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null,pstmt);
        //返回school对象是否修改成功
        return affectedRowNum>0;
    }

    public boolean add(Connection conn,School school) throws SQLException {
        /*//根据连接对象准备调用语句对象，sp_addSchool为存储过程名称，后面为4个参数
        CallableStatement callableStatement=conn.prepareCall("{CALL sp_addSchool(?,?,?,?)}");
        //将第4个参数设置为输出参数，类型为长整型
        callableStatement.registerOutParameter(4,Types.BIGINT);
        callableStatement.setString(1,school.getDescription());
        callableStatement.setString(2,school.getNo());
        callableStatement.setString(3,school.getRemarks());
        //执行可调用语句
        int affectedRowNum = callableStatement.executeUpdate();
        //获取第5个参数的值，数据库为该记录自动生成的id
        int id=callableStatement.getInt(4);
        //为参数school的id字段赋值
        school.setId(id);
        callableStatement.close();*/
        String addDepartment_sql="INSERT INTO school "+"(description,no,remarks)value(?,?,?)";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(addDepartment_sql);
        //为预编译参数赋值
        pstmt.setString(1, school.getDescription());
        pstmt.setString(2, school.getNo());
        pstmt.setString(3, school.getRemarks());
        int rowAffected =pstmt.executeUpdate();
        JdbcHelper.close(null,pstmt);
        return rowAffected > 0;
    }

    public boolean delete(Connection conn,Integer id) throws SQLException {
        //创建sql语句
        String deleteSchool_sql = "DELETE FROM school WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(deleteSchool_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //执行预编译语句的executeUpdate方法,获得删除的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null,pstmt);
        //如果删除记录的行数不为0，返回true,否则返回false
       return affectedRowNum>0;
    }
    public boolean delete(Connection connection,School school) throws SQLException {
        return this.delete(connection,school.getId());
    }
    public int count(Connection connection)throws SQLException {
        String str_sql="SELECT COUNT(id) as cnt_by_school_id FROM school";
        PreparedStatement pstmt=connection.prepareStatement(str_sql);
        ResultSet resultSet=pstmt.executeQuery();
        int count=0;
        while (resultSet.next()) {
            count = resultSet.getInt(1);
        }
        return count;
    }
}